﻿using System;
using System.Data;
using System.IO;

using BLL;
using ExcelLibrary.SpreadSheet;
using Model;
using Utility;

namespace EvaluationSystem
{
    public partial class TestImport : System.Web.UI.Page
    { 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {            

            }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            Boolean fileIsOK = false;
            string path = Server.MapPath("~/userfiles/");

            if (FileUploadDim.HasFile)
            {
                string fileExtension = System.IO.Path.GetExtension(FileUploadDim.FileName).ToLower();
                string[] allowExtension = { ".xls" };

                for (int i = 0; i < allowExtension.Length; i++)
                {
                    if (fileExtension == allowExtension[i])
                    {
                        fileIsOK = true;
                        break;
                    }
                }
                //Check file size < 10M
                if (FileUploadDim.PostedFile.ContentLength > 10240000)
                {
                    fileIsOK = false;
                }

                //Result
                if (fileIsOK)
                {
                    try
                    {
                        FileUploadDim.PostedFile.SaveAs(path + FileUploadDim.FileName);                        
                        
                        //ImportDimension(path + FileUploadDim.FileName);//1.从导入模板中导入数据
                        //ImportQuestion(string.Empty);//2.从劲马提供的文档格式中导入
                        //ImportComment(string.Empty);//3.从劲马提供的文档格式中导入

                        ImportDimension(path + FileUploadDim.FileName);
                        
                        MessageBox.Show("UpLoad Successful!");
                        
                    }
                    catch
                    {
                        MessageBox.Show("UpLoad Failed!");
                    }
                }
                else
                {
                    MessageBox.Show("Specified file length was too large(>10M) for the system.!");
                }

            }
        }

        private bool ImportDimension(string xlsFileNm)
        {
            try
            {
                if (!File.Exists(xlsFileNm)) return false;

                Workbook book = Workbook.Load(xlsFileNm);
                Worksheet sheet = book.Worksheets[0];

                DimensionBLL dimBll = new DimensionBLL();
                Dimension dimModel = new Dimension();

                // traverse rows by Index
                for (int rowIndex = sheet.Cells.FirstRowIndex + 1;//忽略第一行(标题行)
                   rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                {
                    Row row = sheet.Cells.GetRow(rowIndex);

                    dimModel.Dimnm = row.GetCell(1).StringValue;
                    dimModel.Dimension_descrip = row.GetCell(2).StringValue;

                    switch (row.GetCell(3).StringValue)
                    {
                        case "职业能力/技能类":
                            dimModel.Dimension_classid = 1;
                            break;
                        case "职业行为风格类":
                            dimModel.Dimension_classid = 2;
                            break;
                        case "职业智力/潜能类":
                            dimModel.Dimension_classid = 3;
                            break;
                        case "职业态度/动机类":
                            dimModel.Dimension_classid = 4;
                            break;
                        case "质量检测类":
                            dimModel.Dimension_classid = 5;
                            break;
                        case "职业心理基础类":
                            dimModel.Dimension_classid = 6;
                            break;
                        default:
                            break;
                    }

                    dimModel.Time_min = (int)float.Parse(row.GetCell(4).StringValue) ;

                    dimModel.Sinterval = (int)float.Parse(row.GetCell(5).StringValue);
                    dimModel.Minterval = (int)float.Parse(row.GetCell(6).StringValue);
                    dimModel.Linterval = (int)float.Parse(row.GetCell(7).StringValue);

                    dimModel.Dimension_2 = float.Parse(row.GetCell(8).StringValue);
                    dimModel.Dimension_8 = float.Parse(row.GetCell(9).StringValue);
                    dimModel.Dimension_17 = float.Parse(row.GetCell(10).StringValue);
                    dimModel.Dimension_32 = float.Parse(row.GetCell(11).StringValue);
                    dimModel.Dimension_50 = float.Parse(row.GetCell(12).StringValue);
                    dimModel.Dimension_68 = float.Parse(row.GetCell(13).StringValue);
                    dimModel.Dimension_83 = float.Parse(row.GetCell(14).StringValue);
                    dimModel.Dimension_92 = float.Parse(row.GetCell(15).StringValue);
                    dimModel.Dimension_98 = float.Parse(row.GetCell(16).StringValue);

                    dimBll.Add(dimModel);
                }             
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        private bool ImportComment(string xlsFileNm)
        {
            try
            {
                xlsFileNm = @"E:\Project\svn_all\exam-report\需求文档\功能调试版评语库（评语分部仍在增加）.xls";
                if (!File.Exists(xlsFileNm)) return false;

                Workbook book = Workbook.Load(xlsFileNm);

                PositionBLL posBLL = new PositionBLL();
                DimensionBLL dimBLL = new DimensionBLL();
                CommentBLL comBLL = new CommentBLL();
                Comment com = new Comment();

                DataSet dimDS = dimBLL.GetDimensionAll();
                DataSet posDS = posBLL.GetPositionAll(string.Empty);

                foreach (Worksheet sheet in book.Worksheets)
                {
                    string sheetNm = sheet.Name.Trim();
                    int dimID = -1;//在这里加断点可以监视单个维度的导入情况
                    //int posID = 8;//8，服务顾问，
                    int posID = 9;//9，销售顾问。
                    foreach (DataRow row in dimDS.Tables[0].Rows)
                    {
                        if (row["dimension_name"].ToString().Equals(sheetNm))
                        {
                            dimID = Int32.Parse(row["dimension_id"].ToString().Trim());
                            break;
                        }
                    }
                    for (int rowIndex = sheet.Cells.FirstRowIndex + 1;//忽略第一行(标题行)
                        rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                    {
                        Row row = sheet.Cells.GetRow(rowIndex);
                        com.Posion_id = posID;
                        com.Dimension_id = dimID;

                        string score_region = row.GetCell(0).StringValue.Trim();
                        if (score_region.Equals("偏高"))
                        {
                            com.Region_id = 1;
                        }
                        else if (score_region.Equals("适配"))
                        {
                            com.Region_id = 2;
                        }
                        else if (score_region.Equals("稍低"))
                        {
                            com.Region_id = 3;
                        }
                        else if (score_region.Equals("偏低"))
                        {
                            com.Region_id = 4;
                        }

                        for (int i = 1; i < sheet.Cells.LastColIndex + 1; i++)
                        {
                            com.Report_part_id = i;
                            com.Comment_nm = row.GetCell(i).StringValue.Trim();
                            comBLL.Add(com);
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        private bool ImportQuestion(string xlsFileNm)
        {
            try
            {
                xlsFileNm = @"E:\Project\svn_all\exam-report\需求文档\功能调试版题库（维度仍旧在增加）.xls";
                if (!File.Exists(xlsFileNm)) return false;

                Workbook book = Workbook.Load(xlsFileNm);
                //Worksheet sheet = book.Worksheets[0];

                DimensionBLL dimBLL = new DimensionBLL();
                QuestionBLL qesBLL = new QuestionBLL();
                Question question = new Question();

                DataSet dimDS = dimBLL.GetDimensionAll();

                foreach (Worksheet sheet in book.Worksheets)
                {
                    if (sheet.Name.Trim().Equals("说明"))
                    {
                        continue;
                    }
                    string sheetNm = sheet.Name.Trim();
                    int dimID = -1;//在这里加断点可以监视单个维度的导入情况

                    foreach (DataRow row in dimDS.Tables[0].Rows)
                    {
                        if (row["dimension_name"].ToString().Equals(sheetNm))
                        {
                            dimID = Int32.Parse(row["dimension_id"].ToString().Trim());
                            break;
                        }
                    }
                    
                    for (int rowIndex = sheet.Cells.FirstRowIndex + 2;//忽略第一行(标题行)
                            rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                    {
                        Row row = sheet.Cells.GetRow(rowIndex);
                        
                        question.Question_no = Int32.Parse(row.GetCell(0).StringValue.Trim());
                        question.Question_title = row.GetCell(5).StringValue.Trim();
                        question.Question_answer_A = row.GetCell(6).StringValue.Trim();
                        question.Question_answer_B = row.GetCell(7).StringValue.Trim();
                        question.Question_answer_C = row.GetCell(8).StringValue.Trim();
                        question.Question_answer_D = row.GetCell(9).StringValue.Trim();
                        question.Question_answer_E = row.GetCell(10).StringValue.Trim();

                        question.Question_answer_A_score = row.GetCell(11).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(11).StringValue.Trim());
                        question.Question_answer_B_score = row.GetCell(12).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(12).StringValue.Trim());
                        question.Question_answer_C_score = row.GetCell(13).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(13).StringValue.Trim());
                        question.Question_answer_D_score = row.GetCell(14).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(14).StringValue.Trim());
                        question.Question_answer_E_score = row.GetCell(15).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(15).StringValue.Trim());

                        question.Question_typeid = 1;//1 单选
                        question.Question_status = 1;//1 有效

                        if (dimID > 0)
                        {
                            question.Question_dimensionid = dimID;
                        }
                        else
                        {
                            MessageBox.Show("Error");
                            return false;
                        }

                        qesBLL.Add(question);
                    }
                }
                MessageBox.Show("题目导入成功");
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }     
        
    }
}